import pymysql
from functools import wraps


def reConnect(func):

    @wraps(func)
    def wapper(self, *args, **kwargs):
        try:
            self.ping()
        except:
            self.re_connect()
        result = func(self, *args, **kwargs)
        return result

    return wapper


class MySQL(object):

    def __init__(self):
        self.__db = pymysql.connect(
            host='120.24.90.180', user='root', password='1203', port=3306, db='ours_database')
        self.cursor = self.__db.cursor()

    def exit(self):
        self.__db.close()

    def ping(self):
        self.__db.ping()

    def re_connect(self):
        self.__db()

    @reConnect
    def check_staff(self, user_id, passwd):
        """
        检查用户ID与密码是否正确
        :param user_id: 用户id
        :parma passwd: 密码
        :return: 返回bool, 员工id, 员工权限
        """
        print("user_id:" + user_id)
        sql = "SELECT password, emp_num, job from Hr_Person where emp_num='%s'" % user_id
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        if not result:
            return False, '', ''
        get_passwd = result[0]
        get_id = result[1]
        get_job = result[2]
        self.exit()
        if str(get_passwd) == str(passwd):
            return True, get_id, get_job
        else:
            return False, '', ''

    @reConnect
    def check_demand_file(self, file_id):
        sql = "SELECT * FROM Po_Need_File WHERE inquire_id='{}'".format(file_id)
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.exit()
        if not result:
            return False
        else:
            return True

    @reConnect
    def check_item_num(self, item_num):
        # 检测物品编号
        sql = "SELECT * FROM Mtl_Items_All WHERE item_num='{}'".format(item_num)
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.exit()
        if not result:
            return False
        else:
            return True


    @reConnect
    def __store_one(self, table_name, data):
        """
        向数据库中存入一条数据
        :param table_name: 表名
        :param data:数据，字典类型
        """
        flag = False
        data = data
        table = table_name
        keys = ','.join(data.keys())
        values = ','.join(['%s'] * len(data))
        sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(
            table=table, keys=keys, values=values)
        try:
            #print(sql, tuple(data.values()))
            self.cursor.execute(sql, tuple(data.values()))
            self.__db.commit()
            self.exit()
            flag = True
        except Exception as e:
            print('error msg: ',e)
            self.__db.rollback()
            self.exit()
        return flag

    @reConnect
    def store_error(self, _id, error_msg):
        """
        向数据库中存入一条员工数据
        :return: bool
        """
        data = {
            'error_id': _id,
            'error_msg': error_msg
        }
        flag = self.__store_one('ERROR', data)
        return flag

    @reConnect
    def store_staff(self, _id, name, superior_id, job, department, password):
        """
        向数据库中存入一条员工数据
        :return: bool
        """
        jobs = {'采购员': 1, '部门经理': 2, '总经理': 3}
        data = {
            'emp_num': _id,
            'name': name,
            'manager_emp_num': superior_id,
            'job': jobs.get(job),
            'department': department,
            'password': password
        }
        flag = self.__store_one('Hr_Person', data)
        return flag

    @reConnect
    def store_demand(self, _id, department, item_id, item_name, item_count, date, input_person, demand_file_id, state='未采购'):
        """
        :return: flag
        """
        data = {
            'de_rnum': _id,
            'de_name': department,
            'de_ritem_id': item_id,
            'item_name': item_name,
            'de_ritem_count': item_count,
            'de_date': date,
            'de_su': input_person,
            'de_if': state,
            'inquire_id': demand_file_id
        }
        flag = self.__store_one('Po_Need_All', data)
        return flag

    @reConnect
    def store_plan(self, _id, item_name, item_id, total_count, total_money, demand_ids, date, approval_date, approval_staff_id, unit_price, vender_id, state='待审批'):
        """
        :return: flag
        """
        data = {
            'pur_num': _id,
            'item_name': item_name,
            'item_num': item_id,
            'pur_count': total_count,
            'pur_money': total_money,
            'pur_state': state,
            'pur_numbers': demand_ids,
            'pur_dedate': date,
            'pur_apdate': approval_date,
            'emp_num': approval_staff_id,
            'pur_cost': unit_price,
            'vender_code': vender_id
        }
        flag = self.__store_one('Po_Plan_All', data)
        return flag

    @reConnect
    def store_order(self, _id, vender_code, item_id, quantity, receive_quantity, unit_price, total_money, plan_id, state='采购中'):
        """
        :return: flag
        """
        data = {
            'po_number': _id,
            'vender_code': vender_code,
            'status': state,
            'item_code': item_id,
            'quantity': quantity,
            'receive_quantity': receive_quantity,
            'price': unit_price,
            'total_money': total_money,
            'pur_num': plan_id
        }
        flag = self.__store_one('Po_Headers_All', data)
        return flag

    @reConnect
    def store_entry(self, _id, quantity, item_id, date, order_id, staff_id):
        """
        :return flag:
        """
        data = {
            're_count': quantity,
            'item_num': item_id,
            're_date': date,
            'po_number': order_id,
            're_id': _id,
            're_name': staff_id
        }
        flag = self.__store_one('Input', data)
        return flag

    @reConnect
    def store_receive_file(self, count, item_id, receive_date, order_id, _id, content, staff_id):
        """
        :return flag:
        """
        data = {
            'get_count': count,
            'item_num': item_id,
            'get_date': receive_date,
            'po_number': order_id,
            'get_id': _id,
            'get_text': content,
            'emp_num': staff_id,
            'over_num': '0',
        }
        #print(data)
        flag = self.__store_one('Po_Take_File', data)
        return flag

    @reConnect
    def store_demand_file(self, _id, content, create_date):
        """
        :return flag:
        """
        data = {
            'inquire_id': _id,
            'inquire_text': content,
            'inquire_time': create_date
        }
        flag = self.__store_one('Po_Need_File', data)
        return flag

    @reConnect
    def store_contract_file(self, _id, order_id, content, create_date):
        """
        :return flag:
        """
        data = {
            'con_id': _id,
            'po_number': order_id,
            'context': content,
            'con_time': create_date
        }
        flag = self.__store_one('Po_Contract_File', data)
        return flag

    @reConnect
    def store_order_file(self, _id, order_id, content, create_date):
        """
        :return flag:
        """
        data = {
            'or_id': _id,
            'po_number': order_id,
            'context': content,
            'con_time': create_date
        }
        flag = self.__store_one('Po_Order_File', data)
        return flag

    @reConnect
    def __update_one(self, table_name, data, key_name, key):
        """
        更新一条数据
        :param table_name: 表名
        :param data: 需要更新的数据
        :param key_name: 查询的字段
        :parama key: 查询的字段的值
        :return: bool
        """
        flag = False
        ss = ''
        for name, value in data.items():
            ss = ss + ',' + name + ' = ' + "'" + str(value) + "'"
        ss = ss[1:]
        sql = "UPDATE {table} SET {data} WHERE {key_name} = '{key}'".format(
            table=table_name, data=ss, key_name=key_name, key=key)
        # print(sql)
        try:
            self.cursor.execute(sql)
            self.__db.commit()
            self.exit()
            flag = True
        except Exception as e:
            print(e)
            self.__db.rollback()
            self.exit()
        return flag

    def update_demand(self, data, key):
        """
        修改需求表
        :param data: 需要修改的数据，字典类型
        :param key: 需求表的编号
        :return bool:
        """
        flag = self.__update_one('Po_Need_All', data, 'de_rnum', key)
        return flag

    def update_plan(self, data, key):
        """
        更新计划表
        :param data: 需要修改的数据，字典类型
        :param key: 计划表的编号
        :return bool:
        """
        flag = self.__update_one('Po_Plan_All', data, 'pur_num', key)
        return flag

    def update_order(self, data, key):
        """
        更新订单
        :param data: 需要修改的数据，字典类型
        :param key: 订单的编号
        :return bool:
        """
        flag = self.__update_one('Po_Headers_All', data, 'po_number', key)
        return flag

    @reConnect
    def update_order_receive_num(self, order_id, count):
        flag = False
        sql = "UPDATE Po_Headers_All SET receive_quantity=receive_quantity+{count} WHERE po_number = '{order_id}'".format(
            count=int(count), order_id=order_id)
        # print(sql)
        try:
            self.cursor.execute(sql)
            self.__db.commit()
            self.exit()
            flag = True
        except Exception as e:
            print(e)
            self.__db.rollback()
            self.exit()
        return flag

    @reConnect
    def get_order_receive_count(self, _id):
        sql = "SELECT receive_quantity FROM Po_Headers_All WHERE po_number = '{_id}'".format(_id=_id)
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.exit()
        if not result:
            return False
        else:
            return result[0]

    @reConnect
    def get_order_count(self, _id):
        sql = "SELECT quantity FROM Po_Headers_All WHERE po_number = '{_id}'".format(_id=_id)
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.exit()
        if not result:
            return False
        else:
            return result[0]


    @reConnect
    def update_stock(self, item_id, count):
        """
        更新物品库存
        :param item_id: 物品编号
        :param count: 增加的数量
        :return: bool
        """
        # update student set score=score+1 where id = 1
        flag = False
        sql = "UPDATE Mtl_On_Hand SET qty=qty+{count} WHERE item_num = '{item_id}'".format(
            count=int(count), item_id=item_id)
        # print(sql)
        try:
            self.cursor.execute(sql)
            self.__db.commit()
            self.exit()
            flag = True
        except Exception as e:
            print(e)
            self.__db.rollback()
            self.exit()
        return flag

    @reConnect
    def search_demands_of_plan(self, key):
        """
        获取计划表中的的demand_ids字段
        :param key:计划单编号
        :return demand_ids: 相关需求单
        """
        sql = "SELECT pur_numbers from Po_Plan_All where pur_num='%s'" % key
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.exit()
        if not result:
            return False
        else:
            return result[0]

    @reConnect
    def search_plan_of_order(self, key):
        """
        获取订单中的的 计划单号 字段
        :param key:订单编号
        :return plan_id: 计划单编号
        """
        sql = "SELECT pur_num from Po_Headers_All where po_number='%s'" % key
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.exit()
        if not result:
            return False
        else:
            return result[0]

    
    @reConnect
    def search_item_name(self, item_id):
        sql = "SELECT item_name from Mtl_Items_All where item_num='%s'" % item_id
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.exit()
        if not result:
            return False
        else:
            return result[0]


    @reConnect
    def get_demand(self, _id=None):
        """
        获取需求表
        """
        sql = ''

        if _id:
            sql = "SELECT * FROM Po_Need_All WHERE de_rnum = '{_id}'".format(
                _id=_id)
        else:
            sql = "SELECT * FROM Po_Need_All WHERE de_if='未采购'"

        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        self.exit()
        return results

# 11.获取计划单
    @reConnect
    def get_plan(self, pur_num):
        """
        获取计划单
        """
        sql = ''
        if pur_num:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Plan_All WHERE pur_num='%s'" % pur_num
        else:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Plan_All WHERE pur_state='待审批' or pur_state='未通过'"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

    # 12.获取订单
    @reConnect
    def get_order(self, po_number):
        """
        获取订单
        """
        sql = ''
        if po_number:
            sql = "select SQL_NO_CACHE o.*,i.item_name from Po_Headers_All o join Mtl_Items_All i where o.item_code=i.item_num and po_number='%s'" % po_number
        else:
            sql = "select SQL_NO_CACHE o.*,i.item_name from Po_Headers_All o join Mtl_Items_All i where o.item_code=i.item_num"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

    # 13.入库单
    @reConnect
    def get_entry(self, re_id):
        """
        获取入库单
        """
        sql = ''
        if re_id:
            sql = "SELECT SQL_NO_CACHE * FROM Input WHERE re_id='%s'" % re_id
        else:
            sql = "SELECT SQL_NO_CACHE * FROM Input"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

    # 14.获取收货单
    @reConnect
    def get_receive_file(self, get_id):
        """
        根据编号获取收货单文件
        """
        sql = ''
        if get_id:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Take_File WHERE get_id='%s'" % get_id
        else:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Take_File"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

    # 15.需求文件
    @reConnect
    def get_demand_file(self, inquire_id):
        """
        根据编号获取需求文件
        """
        sql = "SELECT SQL_NO_CACHE * FROM Po_Need_File WHERE inquire_id='%s'" % inquire_id
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result
    
    @reConnect
    def get_demand_files(self):
        """
        根据编号获取需求文件
        """
        sql = "SELECT SQL_NO_CACHE * FROM Po_Need_File"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

    # 16.合同文件
    @reConnect
    def get_contract_file(self, con_id):
        """
        根据合同文件编号获取合同文件
        """
        sql = "SELECT SQL_NO_CACHE con_id,po_number,con_time FROM Po_Contract_File WHERE con_id='%s'" % con_id
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        # print(result)
        self.exit()
        return result

    # 17.订单文件
    @reConnect
    def get_order_file(self, or_id):
        """
        获取订单文件
        """
        sql = ''
        if or_id:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Order_File WHERE or_id='%s'" % or_id
        else:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Order_File"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

    # 19.物品信息
    @reConnect
    def get_item(self, item_num):
        """
        获取物品信息
        """
        sql = ''
        if item_num:
            sql = "SELECT SQL_NO_CACHE * FROM Mtl_Items_All WHERE item_num='%s'" % item_num
        else:
            sql = "SELECT SQL_NO_CACHE * FROM Mtl_Items_All"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

    # 20.供应商信息
    @reConnect
    def get_supplier(self, vender_code):
        """
        获取供应商信息
        """
        sql = ''
        if vender_code:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Venders_All WHERE vender_code='%s'" % vender_code
        else:
            sql = "SELECT SQL_NO_CACHE * FROM Po_Venders_All"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result
     # 22.查询员工信息

    @reConnect
    def search_staff(self, id):
        """
        获取员工信息
        """
        sql = ''
        if id:
            sql = "SELECT SQL_NO_CACHE * FROM Hr_Person WHERE emp_num='%s'" % id
        else:
            sql = "SELECT SQL_NO_CACHE * FROM Hr_Person"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

#23.供应商信息修改
    @reConnect
    def update_supplier(self, id, name, address, contact, phone):
        sql = "update Po_Venders_All set vender_name={name}, address={address}, contact={contact}, phone={phone} where vender_code='{id}'".format(
            name=name, address=address, contact=contact, phone=phone, id=id)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result

#24.物品信息修改
    @reConnect
    def update_item(self, id, des, i_type, name):
        sql = "update Mtl_Items_All set item_description={des}, item_type={i_type}, item_name={name} where item_num='{id}'".format(
            des=des, i_type=i_type, name=name, id=id)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result
#25.员工信息修改
    @reConnect
    def update_staff(self, id, name, s_id, job, dept, passwd):
        sql = "update Hr_Person set name={name}, manager_emp_num={s_id}, job={job}, department={dept}, password={passwd} where emp_num='{id}'".format(
            name=name ,s_id=s_id, job=job, dept=dept, passwd=passwd, id=id)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result
#26.需求单修改
    @reConnect
    def update_demand1(self, id, dept, count, time):
        sql = "update Po_Need_All set de_name={dept}, de_ritem_count={count}, de_date={time} where de_rnum='{id}'".format(
            dept=dept, count=count, time=time, id=id)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.exit()
        return result
#27.计划单修改
    @reConnect
    def update_paln1(self, key, it_id, count, unit_price, s_id, state, total_price, __id):
        sql = "update Po_Plan_All set pur_state = \'{state}\' where pur_num = '{key}'".format(state=state, key=key)
        #print(sql)
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        if state == '已通过':
            data = {
                'po_number': __id,
                'vender_code': s_id,
                'status': '采购中',
                'item_code': it_id,
                'quantity': count,
                'receive_quantity': 0,
                'price': unit_price,
                'total_money': total_price,
                'pur_num': key
            }
            flag = self.__store_one('Po_Headers_All', data)
        if flag and result:
            return result
#物品表现在有几行
    @reConnect
    def get_item_row(self):
        """
        获取物品表有几行
        """
        sql = "SELECT item_num FROM Mtl_Items_All" 
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        lens = len(result)
        print(lens)
        self.exit()
        return lens

    @reConnect
    def store_item(self, _id, des, i_type, name):
        """
        :return flag:
        """
        data = {
            'item_num': _id,
            'item_description': des,
            'item_type': i_type,
            'item_name': name
        }
        flag = self.__store_one('Mtl_Items_All', data)
        return flag